from backend.api.connection import POOL
# from connection import POOL   # 单独运行用这个
import datetime


# 新增笔记
# 返回
# {'nid': 0
#  'ok': 1,
#  'msg':''}
def addNote(word, content, creator):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'nid': 0, 'ok': 0, 'msg': ''}

    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    sql = """INSERT INTO notelist (
            word, content,
            creator,
            create_time,edit_time,
            approve) VALUES(%s, %s, %s, %s, %s, %s)"""
    cur.execute(sql, (word,
                      content,
                      creator,
                      dt, dt,
                      0))

    nid = int(cur.lastrowid)
    conn.commit()
    cur.close()
    response['nid'] = nid
    response['ok'] = 1
    return response


# 修改笔记
# 返回
# {
#  'ok': 1,
#  'msg':''}
def updateNote(nid, content):
    # content = repr(content)
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'ok': 0, 'msg': ''}
    edit_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    sql = "UPDATE notelist SET content = %s,edit_time = %s WHERE nid = %s"
    cur.execute(sql, (content,
                      edit_time,
                      nid))
    conn.commit()
    response['ok'] = 1
    print(response)
    return response


# 按用户筛选笔记
# 返回
# {'data': [{nid:111,word:"bed",content:"...",
#           create_time:2000-01-01 00:00:00,
#            edit_time:2000-01-01 00:00:00,approve:1}……
#           ],
#  'ok': 1,
#  'msg':''}
def getNoteByUser(uid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}

    sql = '''SELECT nid, word, content, create_time, edit_time,
            approve FROM notelist WHERE creator = {}'''.format(uid)
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    # print(resultset)
    for result in resultset:
        note = {'nid': 0, 'word': '', 'content': '', 'create_time': "", 'edit_time': "",
                'approve': 0}
        note['nid'] = result[0]
        note['word'] = result[1]
        note['content'] = result[2]
        note['create_time'] = datetime.datetime.strftime(
            result[3], '%Y-%m-%d %H:%M:%S')
        note['edit_time'] = datetime.datetime.strftime(
            result[4], '%Y-%m-%d %H:%M:%S')
        note['approve'] = result[5]
        data.append(note)

    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response

# 按单词筛选笔记
# 返回
# {'data': [{nid:111,word:"bed",content:"...",creator:10086, 'username':'',
#           create_time:2000-01-01 00:00:00,
#            edit_time:2000-01-01 00:00:00,approve:1}……
#           ],
#  'ok': 1,
#  'msg':''}


def getNoteByWord(word):
    word = repr(word)
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}

    sql = '''SELECT n.nid, n.word, n.content, n.creator, n.create_time, n.edit_time,
            n.approve, u.username FROM notelist n, userinfo u
            WHERE word = {} AND u.uid = n.creator'''.format(word)
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    # print(resultset)
    for result in resultset:
        note = {'nid': 0, 'word': '', 'content': '', 'creator': 0, 'username':'',
                'create_time': "", 'edit_time': "", 'approve': 0}
        note['nid'] = result[0]
        note['word'] = result[1]
        note['content'] = result[2]
        note['creator'] = result[3]
        note['username'] = result[7]
        note['create_time'] = datetime.datetime.strftime(
            result[4], '%Y-%m-%d %H:%M:%S')
        note['edit_time'] = datetime.datetime.strftime(
            result[5], '%Y-%m-%d %H:%M:%S')
        note['approve'] = result[6]
        data.append(note)

    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response

# 获取未审核笔记
# 返回
# {'data': [{nid:111,word:"bed",content:"...",creator:10086, 'username':'',
#           create_time:2000-01-01 00:00:00,
#            edit_time:2000-01-01 00:00:00,approve:1}……
#           ],
#  'ok': 1,
#  'msg':''}
def getUnCheckedNote():
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}

    sql = '''SELECT n.nid, n.word, n.content, n.creator, n.create_time, n.edit_time,
            n.approve, u.username FROM notelist n, userinfo u
            WHERE n.approve = 0 AND u.uid = n.creator'''
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    # print(resultset)
    for result in resultset:
        note = {'nid': 0, 'word': '', 'content': '', 'creator': 0, 'username':'',
                'create_time': "", 'edit_time': "", 'approve': 0}
        note['nid'] = result[0]
        note['word'] = result[1]
        note['content'] = result[2]
        note['creator'] = result[3]
        note['username'] = result[7]
        note['create_time'] = datetime.datetime.strftime(
            result[4], '%Y-%m-%d %H:%M:%S')
        note['edit_time'] = datetime.datetime.strftime(
            result[5], '%Y-%m-%d %H:%M:%S')
        note['approve'] = result[6]
        data.append(note)

    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response


# 审核笔记
# 返回
# {
#  'ok': 1,
#  'msg':''}
def checkNote(nid, approve):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'ok': 0, 'msg': ''}

    sql = "UPDATE notelist SET approve = {} WHERE nid = {}".format(approve, nid)
    cur.execute(sql)
    conn.commit()
    cur.close()

    response['ok'] = 1
    return response


if __name__ == '__main__':
    # addPlan(1, 1, 30)
    # getPlanList(1)
    # loadPlan(1)
    pass
